import datetime

from odoo import fields, models, api
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT


class PurchaseSalesReport(models.Model):
    _name = 's.purchase.sales.summary.report'
    _description = 'Purchase and sales report'
    _auto = False

    product_id = fields.Many2one('product.product', string='Product')
    default_code = fields.Char('Code', related='product_id.default_code')
    category_id = fields.Many2one('product.category')
    beginning_qty = fields.Float('Beginning Qty')
    # beginning_price = fields.Float('Beginning Rate', compute='_compute_value')
    # beginning_value = fields.Float('Beginning Value', compute='_compute_value')
    beginning_uom_id = fields.Many2one('uom.uom', 'Beginning Unit', related='product_id.uom_id')
    stock_in_qty = fields.Float('In Qty')
    # stock_in_price = fields.Float('In Rate')
    # stock_in_value = fields.Float('In Value')
    stock_in_uom_id = fields.Many2one('uom.uom', 'Beginning Unit', related='product_id.uom_id')
    stock_out_qty = fields.Float('Out Qty')
    # stock_out_price = fields.Float('Out Rate')
    # stock_out_value = fields.Float('Out Value')
    stock_out_uom_id = fields.Many2one('uom.uom', 'Beginning Unit', related='product_id.uom_id')
    ending_qty = fields.Float('Ending Qty')
    # ending_price = fields.Float('Ending Rate', compute='_compute_value')
    # ending_value = fields.Float('Ending Value', compute='_compute_value')
    ending_uom_id = fields.Many2one('uom.uom', 'Ending Unit', related='product_id.uom_id')

    # def _compute_value(self):
    #     product_ids = self.mapped('product_id')
    #     start_date = self._context.get('start_date')
    #     result = product_ids._compute_quantities_dict(None, None, None, to_date=start_date)
    #     for rec in self:
    #         rec_dict = result.get(rec.product_id.id, {})
    #         beginning_qty = rec_dict.get('qty_available', 0)
    #         ending_qty = beginning_qty + rec.stock_in_qty - rec.stock_out_qty
    #
    #         valuation_layers = self.env['stock.valuation.layer'].search([
    #             ('product_id', '=', rec.product_id.id),
    #             ('create_date', '<=', start_date)
    #         ], order='create_date DESC')
    #         beginning_value = sum(valuation_layers.mapped('value'))
    #         total = sum(valuation_layers.mapped('quantity'))
    #         beginning_price = beginning_value / total if total else 0
    #
    #         ending_value = beginning_value + rec.stock_in_value - rec.stock_out_value
    #         ending_price = ending_value / ending_qty if ending_qty else 0
    #
    #         rec.update({
    #             'beginning_qty': beginning_qty,
    #             'beginning_price': beginning_price,
    #             'beginning_value': beginning_value,
    #             'ending_qty': ending_qty,
    #             'ending_price': ending_price,
    #             'ending_value': ending_value,
    #         })

    @property
    def _table_query(self):
        start_date = self._context.get('start_date')
        end_date = self._context.get('end_date')
        product_category_id = self._context.get('product_category_id')
        if not start_date:
            start_date = '2023-01-01 00:00:00'
        if not end_date:
            end_date = datetime.datetime.now().strftime(DEFAULT_SERVER_DATETIME_FORMAT)
        category_where = ''
        if product_category_id:
            category_where = f' and (pc.id = {product_category_id} or pc.parent_id = {product_category_id})'

        sql = f"""
            with cte as (select sml.product_id,
                    sml.date                         sml_date,
                    sml.quantity / uu.factor AS quantity,
                    pt.uom_id                           uu_id

             from stock_move_line sml
                      left join stock_location sl1 on sml.location_id = sl1.id
                      left join stock_location sl2 on sml.location_dest_id = sl2.id
                      left join product_product pp on sml.product_id = pp.id
                      left join product_template pt on pp.product_tmpl_id = pt.id
                      left join product_category pc on pt.categ_id = pc.id
                      left join uom_uom uu on sml.product_uom_id = uu.id
             where sml.state = 'done'
               and sml.date <= '{end_date}'
               and pp.active = TRUE
               and sl2.id in
                   (select id
                    from stock_location
                    where usage = 'internal'
                      and name not in ('生产前', '开料仓', '压合仓', '油墨仓', '线路仓'))
               and sl1.id not in (select id
                                  from stock_location
                                  where usage = 'internal'
                                    and name not in ('生产前', '开料仓', '压合仓', '油墨仓', '线路仓'))
             

             union all
             select sml.product_id,
                    sml.date                              sml_date,
                    sml.quantity / uu.factor * -1 AS quantity,
                    pt.uom_id                           uu_id
             from stock_move_line sml
                      left join stock_move sm on sml.move_id = sm.id
                      left join stock_location sl1 on sml.location_id = sl1.id
                      left join stock_location sl2 on sml.location_dest_id = sl2.id
                      left join product_product pp on sml.product_id = pp.id
                      left join product_template pt on pp.product_tmpl_id = pt.id
                      left join product_category pc on pt.categ_id = pc.id
                      left join uom_uom uu on sml.product_uom_id = uu.id
             where sml.state = 'done'
               and sml.date <= '{end_date}'
               and pp.active = TRUE
               and sl1.id in
                   (select id
                    from stock_location
                    where usage = 'internal'
                      and name not in ('生产前', '开料仓', '压合仓', '油墨仓', '线路仓'))
               and sl2.id not in (select id
                                  from stock_location
                                  where usage = 'internal'
                                    and name not in ('生产前', '开料仓', '压合仓', '油墨仓', '线路仓'))
             ),
     beginning as (select product_id,
                          round(sum(quantity), 3) qty,
                          uu_id
                   from cte
                   where sml_date <= '{start_date}'
                   group by product_id, uu_id),
     ending as (select product_id,
                       round(sum(quantity), 3) qty,
                       uu_id
                from cte
                where sml_date <= '{end_date}'
                group by product_id, uu_id),
     move_in as (select sml.product_id                   product_id,
                        sum(sml.quantity / uu.factor) as quantity,
                        pt.uom_id                        uom_id,
                        sm.id                            in_move_id
                 from stock_move_line sml
                          left join stock_move sm on sml.move_id = sm.id
                          left join stock_location sl1 on sml.location_id = sl1.id
                          left join stock_location sl2 on sml.location_dest_id = sl2.id
                          left join product_product pp on sml.product_id = pp.id
                          left join product_template pt on pp.product_tmpl_id = pt.id
                          left join uom_uom uu on sml.product_uom_id = uu.id

                 where sml.state = 'done'
                   and sl2.id in
                       (select id
                        from stock_location
                        where usage = 'internal'
                          and name not in ('生产前', '开料仓', '压合仓', '油墨仓', '线路仓'))
                   and sl1.id not in (select id
                                      from stock_location
                                      where usage = 'internal'
                                        and name not in ('生产前', '开料仓', '压合仓', '油墨仓', '线路仓'))
                   and sml.date > '{start_date}'
                   and sml.date < '{end_date}'
                 group by sml.product_id, pt.uom_id, sm.id),
     move_in_two as (select move_in.product_id,
                            move_in.uom_id                         in_uom_id,
                            sum(move_in.quantity)                  in_total,
                            sum(svl.value)                         stock_in_value,
                            sum(svl.value) / sum(move_in.quantity) stock_in_price
                     from move_in
                              left join stock_valuation_layer svl on move_in.in_move_id = svl.stock_move_id
                     group by move_in.product_id, move_in.uom_id),
     move_out as (select sml.product_id                   product_id,
                         sum(sml.quantity / uu.factor) as quantity,
                         pt.uom_id                     as uom_id,
                         sm.id                            out_sm_id
                  from stock_move_line sml
                           left join stock_move sm on sml.move_id = sm.id
                           left join stock_location sl1 on sml.location_id = sl1.id
                           left join stock_location sl2 on sml.location_dest_id = sl2.id
                           left join product_product pp on sml.product_id = pp.id
                           left join product_template pt on pp.product_tmpl_id = pt.id
                           left join uom_uom uu on sml.product_uom_id = uu.id

                  where sml.state = 'done'
                    and sl1.id in
                        (select id
                         from stock_location
                         where usage = 'internal'
                           and name not in ('生产前', '开料仓', '压合仓', '油墨仓', '线路仓'))
                    and sl2.id not in (select id
                                       from stock_location
                                       where usage = 'internal'
                                         and name not in ('生产前', '开料仓', '压合仓', '油墨仓', '线路仓'))
                    and sml.date > '{start_date}'
                    and sml.date < '{end_date}'
                  group by sml.product_id, pt.uom_id, sm.id),
     move_out_two as (select move_out.product_id,
                             move_out.uom_id,
                             sum(move_out.quantity)                       out_total,
                             abs(sum(svl.value))                          stock_out_value,
                             abs(sum(svl.value) / sum(move_out.quantity)) stock_out_price
                      from move_out
                               left join stock_valuation_layer svl on move_out.out_sm_id = svl.stock_move_id
                      group by move_out.product_id, move_out.uom_id),
        main as(
        select main_pp.id                   id,
               main_pp.id                   product_id,
               main_pt.categ_id             category_id,
               beginning.qty                beginning_qty,
               move_in_two.in_total         stock_in_qty,
               move_in_two.stock_in_price   stock_in_price,
               move_in_two.stock_in_value   stock_in_value,
               move_out_two.out_total       stock_out_qty,
               move_out_two.stock_out_price stock_out_price,
               move_out_two.stock_out_value stock_out_value,
               ending.qty                   ending_qty
        from product_product main_pp
                 left join product_template main_pt on main_pp.product_tmpl_id = main_pt.id
                 left join product_category pc on pc.id = main_pt.categ_id
                 left join beginning on beginning.product_id = main_pp.id
                 left join ending on main_pp.id = ending.product_id
                 left join move_in_two on main_pp.id = move_in_two.product_id
                 left join move_out_two on move_out_two.product_id = main_pp.id
        where main_pt.type != 'service'
              and main_pt.active = true
              and pc.code not in ('BCP', 'CHP')
                {category_where}
                )
                select * from main where NOT (
                    COALESCE(beginning_qty, 0) = 0 
                    AND COALESCE(stock_in_qty, 0) = 0 
                    AND COALESCE(stock_out_qty, 0) = 0 
                    AND COALESCE(ending_qty, 0) = 0
                )
        """
        return sql
